const sql = require("mssql");
const baseFn = require("../../public/base.js");
/**
 *
 * 平板拣货系统后台
 */

/**
 *
 * 获取单品接单信息
 */
// 单品订单一次可接的数量
const getOrderCount = 24;
// 合并单品订单
const mergeOrder = orderList => {
  let getOrderList = orderList.recordset;
  let getOrderResult = [];
  getOrderList.forEach(item => {
    let isExist = getOrderResult.findIndex(shop => item.YPBM === shop.YPBM);
    if (isExist > -1) {
      getOrderResult[isExist].idList.push(item.ID);
      if (item.SL && typeof item.SL === "number") {
        getOrderResult[isExist].SL += item.SL;
      }
    } else {
      if (!item.idList) {
        item.idList = [];
      }
      item.idList.push(item.ID);
      getOrderResult.push(item);
    }
  });
  return getOrderResult;
};
// 补全时间
const repairTime = time => {
  return time.toString().length == 1 ? "0" + time : time;
};
// 获取舍弃分钟的个位的时间
const getMinuteTime = minute => {
  let nowTime = new Date();
  let y = nowTime.getFullYear();
  let M = nowTime.getMonth() + 1;
  let d = nowTime.getDate();
  let h = nowTime.getHours();
  let m = 0;
  // 相隔10分钟的间隔
  if (minute === 10) {
    m = Math.floor(nowTime.getMinutes() / 10) * 10;
  } else if (minute === 5) {
    // 相隔5分钟的间隔
    let getNewM = nowTime.getMinutes();
    let getmOldM = Math.round(nowTime.getMinutes() / 10) * 10;
    m = getmOldM <= getNewM ? getmOldM : getmOldM - 5;
  }

  return `${y.toString()}-${repairTime(M)}-${repairTime(d)} ${repairTime(
    h
  )}:${repairTime(m)}:00.000`;
};
const getLimitTime = () => {
  return getMinuteTime(10);
  // return new Date().getHours() >= 12 ? getMinuteTime(5) : getMinuteTime(10);
};
// 限制的接单时间
const getPickGoodsSingleOrder = async (ctx, next) => {
  try {
    let limitTime = getLimitTime();
    const body = ctx.request.body;
    // 如果是早上取10分钟的时间间隔，下午取5分钟的时间间隔
    let actionSql = `SELECT * FROM ( select m.YPBM,m.YPMC,m.YPGG,m.JLDW,m.ID,m.JXMC,m.SCCJ,MIN(m.HWBH) AS HWBH,y.CCTJ,SUM(m.SL) as SL from XSD_MX  m
        left join XSD_ZB z on m.id=z.id LEFT JOIN BM_YP y ON m.YPBM = y.BM   where z.SH = 'T' AND z.ID_DHD <> '' AND
        z.ID not in (SELECT ID FROM FPDD) AND z.ID IN (	SELECT T1.ID FROM (SELECT ID,YPBM FROM XSD_MX 
        GROUP BY ID,YPBM)T1 GROUP BY T1.ID  HAVING(COUNT(T1.YPBM)=1))  AND  z.RQ>='${baseFn.startTime}' `;
    if (new Date().getHours() <= 12) {
      actionSql += ` AND z.RQ <= '${limitTime}' `;
    }
    actionSql += ` GROUP BY m.ID,m.YPBM,m.YPMC,m.YPGG,m.JLDW,m.JXMC,y.CCTJ,m.SCCJ) T3 ORDER BY T3.HWBH `;
    let orderList = await sql.query(actionSql);

    if (orderList.rowsAffected[0] > 0) {
      let getOrderResult = mergeOrder(orderList);
      let lastOrderResult = [];
      if (getOrderResult.length >= getOrderCount) {
        lastOrderResult = getOrderResult.filter(
          (item, ind) => ind < getOrderCount
        );
      } else {
        lastOrderResult = getOrderResult;
      }
      let insertFpddSql = `INSERT INTO FPDD (ID) `;
      let insertIds = "";
      for (let item of lastOrderResult) {
        for (let ids of item.idList) {
          insertIds += ids + ",";
          // let insertFpdd = await sql.query(`INSERT INTO FPDD VALUES ('${ids}')`);
          insertFpddSql += ` SELECT '${ids}' UNION `;

          let time = setTimeout(async () => {
            let isGetOrderSql = `SELECT COUNT(*) as num FROM YHDD WHERE id = '${ids}'`;
            let isGetOrder = await sql.query(isGetOrderSql);
            if (isGetOrder.recordset[0].num == 0) {
              let deleteSql = `DELETE FROM FPDD WHERE ID = '${ids}'`;
              sql.query(deleteSql);
            }
            clearTimeout(time);
          }, 300000);
        }
      }
      let lastInsertFpddSql = insertFpddSql.slice(
        0,
        insertFpddSql.lastIndexOf("UNION")
      );
      let lastInsertFpddResult = await sql.query(lastInsertFpddSql);
      if (lastInsertFpddResult.rowsAffected[0] > 0) {
        ctx.body = { success: true, result: lastOrderResult };
      } else {
        ctx.body = { success: false };
      }
    } else {
      ctx.body = { success: false };
    }
  } catch (err) {
    // 接了重复订单
    ctx.body = { success: 1 };
  }
};
/**
 * 获得单品订单
 *  */
const havePickGoodsSingleOrder = async (ctx, next) => {
  try {
    const body = ctx.request.body;
    let regCarNum = /^[A-Z]\d{3}$/g;
    if (regCarNum.test(body.carNum)) {
      let idStr = body.orderIdList
        .map(item => {
          return `'${item}'`;
        })
        .join(",");
      let selectShopSql = `SELECT YPBM,SCPH,ID FROM XSD_MX WHERE ID in (${idStr})`;
      let orderInfo = await sql.query(selectShopSql);
      let orderInfoResult = orderInfo.recordset;
      let insertSql = `INSERT INTO YHDD (userid,id,carid,YPBM,JHZT,SCPH,FHZT,SFYC,JHRQ,FHRQ) `;
      // orderInfoResult.forEach((item, ind) => {
      //     insertSql += `SELECT '${body.userId}','${item.ID}','${body.carNum}','${item.YPBM}','F','${item.SCPH}','F','F','','' `;
      //     if (ind !== orderInfoResult.length - 1) {
      //         insertSql += ` UNION `;
      //     }
      // });
      for (let ind in orderInfoResult) {
        let isExist = await sql.query(
          ` SELECT COUNT(id) AS px FROM YHDD WHERE id = '${orderInfoResult[ind].ID}' AND YPBM = '${orderInfoResult[ind].YPBM}' AND SCPH = '${orderInfoResult[ind].SCPH}' `
        );
        if (isExist.recordset[0].px <= 0) {
          insertSql += `SELECT '${body.userId}','${orderInfoResult[ind].ID}','${body.carNum}','${orderInfoResult[ind].YPBM}','F','${orderInfoResult[ind].SCPH}','F','F','','' `;
        }
        if (ind != orderInfoResult.length - 1) {
          insertSql += ` UNION `;
        }
      }
      let result = await sql.query(insertSql);
      if (result.rowsAffected[0] > 0) {
        ctx.body = { success: true };
      } else {
        ctx.body = { success: false };
      }
    } else {
      ctx.body = { success: false };
    }
  } catch (err) {
    ctx.body = { success: false };
  }

  // let regCarNum = /^[A-Z]\d{3}$/g;
  // if (regCarNum.test(body.carNum)) {
  //     let isExistSql = `SELECT COUNT(id) AS num FROM YHDD WHERE id = '${body.orderId}'`;
  //     let isExistResult = await sql.query(isExistSql);

  //     if (isExistResult.recordset[0].num == 0) {
  //         let selectShopSql = `SELECT YPBM,SCPH FROM XSD_MX WHERE ID = '${body.orderId}'`;
  //         let allShop = await sql.query(selectShopSql);
  //         let insertSql = `INSERT INTO YHDD (userid,id,carid,YPBM,JHZT,SCPH,FHZT,SFYC,JHRQ,FHRQ) `;
  //         allShop.recordset.forEach((item, ind) => {
  //             if (ind == allShop.recordset.length - 1) {
  //                 insertSql += `SELECT '${body.userId}','${body.orderId}','${body.carNum}','${item.YPBM}','F','${item.SCPH}','F','F','','' `
  //             } else {
  //                 insertSql += `SELECT '${body.userId}','${body.orderId}','${body.carNum}','${item.YPBM}','F','${item.SCPH}','F','F','','' UNION `
  //             }
  //         });
  //         let result = await sql.query(insertSql);
  //         if (result.rowsAffected[0] > 0) {
  //             ctx.body = { success: true }
  //         } else {
  //             ctx.body = { success: false }
  //         }
  //     } else {
  //         ctx.body = { success: false }
  //     }km
  // } else {
  //     ctx.body = { success: false }
  // }
};

/**
 * 获得接单员的所有已接订单
 */
const getPickGoodsUserAllSingleOrder = async (ctx, next) => {
  const body = ctx.request.body;

  // let selectOrderSql = `SELECT Y.YPBM,M.YPMC,M.YPGG,M.JLDW,SUM(M.SL) AS SL FROM YHDD Y LEFT JOIN XSD_MX M ON Y.id = M.ID LEFT JOIN (SELECT COUNT(T2.ID) AS SPSL,T2.ID FROM (SELECT ID FROM XSD_MX GROUP BY ID,YPBM)T2 GROUP BY T2.ID) T1 ON Y.id = T1.ID WHERE T1.SPSL = 1 AND Y.userId = ${body.userId} AND  JHZT = 'F' AND  SFYC = 'F' GROUP BY Y.YPBM,M.YPMC,M.YPGG,M.JLDW `;
  // let selectOrderSql = `SELECT Y.YPBM,Y.JHZT,Y.carId,M.YPMC,M.YPGG,M.JLDW,SUM(M.SL) AS SL FROM YHDD Y LEFT JOIN  XSD_MX  M ON Y.id = M.ID AND Y.YPBM = M.YPBM
  // AND Y.SCPH = M.SCPH LEFT JOIN (SELECT COUNT(T2.ID) AS SPSL,T2.ID FROM (SELECT ID FROM XSD_MX GROUP BY ID,YPBM)T2 GROUP BY T2.ID) T1
  // ON Y.id = T1.ID WHERE T1.SPSL = 1 AND Y.userId = ${body.userId} AND  JHZT = 'F' AND  SFYC = 'F' GROUP BY Y.YPBM,M.YPMC,M.YPGG,M.JLDW,Y.carId,Y.JHZT`;
  let selectOrderSql = `SELECT M.HWBH,Y.YPBM,Y.JHZT,Y.carId,M.YPMC,M.YPGG,M.JLDW,SUM(M.SL) AS SL  FROM YHDD Y 
    LEFT JOIN XSD_MX M ON Y.id = M.ID AND Y.YPBM = M.YPBM AND Y.SCPH = M.SCPH  WHERE  Y.ID IN (SELECT T1.ID FROM (SELECT ID,YPBM FROM YHDD  
    GROUP BY ID,YPBM)T1 GROUP BY T1.ID  HAVING(COUNT(T1.YPBM)=1)) AND  Y.userId = ${body.userId} AND Y.JHZT = 'F' AND  Y.SFYC = 'F'  
    GROUP BY M.HWBH,Y.YPBM,M.YPMC,M.YPGG,M.JLDW,Y.carId,Y.JHZT ORDER BY M.HWBH`;
  let result = await sql.query(selectOrderSql);
  ctx.body = { result: result.recordset };
};

// 获得所有接到的单品订单的信息
const getAllSingleOrderInfo = async (ctx, next) => {
  const body = ctx.request.body;
  // let selectOrderSql = `SELECT Y.YPBM,M.YPMC,M.YPGG,M.JLDW,SUM(M.SL) AS SL FROM YHDD Y LEFT JOIN XSD_MX M ON Y.id = M.ID LEFT JOIN (SELECT COUNT(T2.ID) AS SPSL,T2.ID FROM (SELECT ID FROM XSD_MX GROUP BY ID,YPBM)T2 GROUP BY T2.ID) T1 ON Y.id = T1.ID WHERE T1.SPSL = 1 AND Y.userId = ${body.userId} AND  JHZT = 'F' AND  SFYC = 'F' GROUP BY Y.YPBM,M.YPMC,M.YPGG,M.JLDW `;
  // let selectOrderSql = `SELECT Y.YPBM,Y.JHZT,Y.carId,M.YPMC,M.YPGG,M.JLDW,SUM(M.SL) AS SL FROM YHDD Y LEFT JOIN  XSD_MX  M ON Y.id = M.ID AND Y.YPBM = M.YPBM
  // AND Y.SCPH = M.SCPH LEFT JOIN (SELECT COUNT(T2.ID) AS SPSL,T2.ID FROM (SELECT ID FROM XSD_MX GROUP BY ID,YPBM)T2 GROUP BY T2.ID) T1
  // ON Y.id = T1.ID WHERE T1.SPSL = 1 AND Y.userId = ${body.userId} AND  FHZT = 'F' AND  SFYC = 'F' GROUP BY Y.YPBM,M.YPMC,M.YPGG,M.JLDW,Y.carId,Y.JHZT`;
  let selectOrderSql = `SELECT M.HWBH,Y.YPBM,Y.JHZT,Y.carId,M.YPMC,M.YPGG,M.JLDW,SUM(M.SL) AS SL,
    (SELECT yh.SCPH+',' FROM dbo.YHDD AS yh WHERE yh.YPBM=y.YPBM AND yh.carId=y.carId AND yh.FHZT = 'F' AND yh.SFYC = 'F' GROUP BY yh.SCPH FOR XML PATH('') ) AS SCPH 
    FROM YHDD Y LEFT JOIN XSD_MX M ON Y.id = M.ID AND Y.YPBM = M.YPBM AND Y.SCPH = M.SCPH  WHERE  Y.ID IN (SELECT T1.ID FROM (SELECT ID,YPBM FROM YHDD 
    GROUP BY ID,YPBM)T1 GROUP BY T1.ID  HAVING(COUNT(T1.YPBM)=1)) AND  Y.userId = ${body.userId} AND Y.FHZT = 'F' AND  Y.SFYC = 'F'  
    GROUP BY M.HWBH,Y.YPBM,M.YPMC,M.YPGG,M.JLDW,Y.carId,Y.JHZT`;
  let result = await sql.query(selectOrderSql);
  ctx.body = { result: result.recordset };
};

/**
 *
 * 获得可接单品订单的数量
 */
const getPickGoodSingleOrderCount = async (ctx, next) => {
  // let actionSql = `select m.YPBM,m.ID,SUM(m.SL) as SL from
  // (select YPBM,YPMC,YPGG,JLDW,ID,JXMC,HWBH,SUM(SL) AS SL FROM XSD_MX GROUP BY YPBM,YPMC,YPGG,JLDW,ID,JXMC,HWBH) m
  // left join XSD_ZB z on m.id=z.id  where z.SH = 'T' AND z.ID_DHD <> '' AND
  // z.ID not in (SELECT ID FROM FPDD)  AND  z.RQ>='${baseFn.startTime}'
  // GROUP BY m.ID,m.YPBM,m.ID HAVING(COUNT(m.YPBM)=1)`;
  let limitTime = getLimitTime();
  let actionSql = ` SELECT COUNT(T2.YPBM) AS PX FROM	(select m.YPBM from
        XSD_MX  m
       left join XSD_ZB z on m.id=z.id LEFT JOIN BM_YP y ON m.YPBM = y.BM   where z.SH = 'T' AND z.ID_DHD <> '' AND
       z.ID not in (SELECT ID FROM FPDD) AND z.ID IN (      SELECT T1.ID FROM (SELECT ID,YPBM FROM XSD_MX
           GROUP BY ID,YPBM)T1 GROUP BY T1.ID  HAVING(COUNT(T1.YPBM)=1))  AND  z.RQ>='${baseFn.startTime}' `;
  if (new Date().getHours() <= 12) {
    actionSql += ` AND z.RQ <= '${limitTime}' `;
  }
  actionSql += ` GROUP BY m.YPBM) T2 `;

  let orderList = await sql.query(actionSql);

  ctx.body = { result: orderList.recordset[0].PX };
};

// 获得单品订单的详情
const getSingleOrderDetailInfo = async (ctx, next) => {
  let { userId, shopItem } = ctx.request.body;
  let selectSql = `SELECT M.SCCJ,M.HWBH,M.YPBM,M.YPMC,M.SCPH,M.YPGG,B.CCTJ,M.JXMC,M.PZWH,M.JLDW,Y.carId,SUM(M.SL) AS SL,COUNT(Y.ID) AS DDSL,KC.KCSL FROM YHDD Y LEFT JOIN XSD_MX M 
    ON Y.ID = M.ID AND Y.YPBM = M.YPBM AND Y.SCPH = M.SCPH LEFT JOIN BM_YP B ON Y.YPBM = B.BM 
    LEFT JOIN (SELECT YPBM,SUM(KCSL) AS KCSL FROM KC_KCDTB WHERE BMBM = '07' GROUP BY YPBM) AS KC ON KC.YPBM = Y.YPBM
     WHERE Y.YPBM = '${shopItem.YPBM}' AND Y.carId = '${shopItem.carId}' 
    AND Y.userid = ${userId} AND Y.JHZT = 'F' AND Y.SFYC = 'F'  GROUP BY M.YPBM,M.YPMC,M.SCPH,M.YPGG,M.JLDW,M.HWBH,Y.carId,M.SCCJ,B.CCTJ,M.JXMC,M.PZWH,KC.KCSL`;
  let result = await sql.query(selectSql);
  ctx.body = { result: result.recordset };
};

// 拣货完成改变状态
const pickSingleGoods = async (ctx, next) => {
  let body = ctx.request.body;
  let updateSql = `UPDATE YHDD SET JHZT = 'T',JHRQ = '${baseFn.dateFomat(
    "yyyy-MM-dd hh:mm:ss",
    new Date()
  )}' ,JHZL = ${body.count}
    WHERE userid = '${body.userId}'  AND YPBM = '${body.YPBM}' AND carId = '${
    body.carId
  }'`;
  let result = await sql.query(updateSql);
  if (result.rowsAffected[0] > 0) {
    ctx.body = { success: true };
  } else {
    ctx.body = { success: false };
  }
};

// 单品拣货异常处理

const pickSingleOrderAbnormal = async (ctx, next) => {
  let body = ctx.request.body;
  const { shopInfo, person, userId, reason } = body;
  let allOrderSql = `SELECT * FROM YHDD WHERE userid = ${userId} AND carid = '${shopInfo.carId}' AND YPBM = '${shopInfo.YPBM}' AND JHZT = 'F' AND SFYC = 'F'`;
  let allOrderResult = await sql.query(allOrderSql);
  let allOrder = allOrderResult.recordset;
  let lastResult = false;
  if (0 in allOrder) {
    for (let item of allOrder) {
      let insertSql = `INSERT INTO JHCY VALUES('${
        item.id
      }','${person}','${reason}','${item.YPBM}','${
        item.SCPH
      }','${baseFn.dateFomat("yyyy-MM-dd hh:mm:ss", new Date())}','')`;
      let insertResult = await sql.query(insertSql);
      let updateSql = `UPDATE YHDD SET SFYC = 'T' WHERE userid = ${item.userid} AND id = '${item.id}' AND YPBM = '${item.YPBM}' AND SCPH = '${item.SCPH}'`;
      let updateResult = await sql.query(updateSql);
      if (
        insertResult.rowsAffected[0] > 0 &&
        updateResult.rowsAffected[0] > 0
      ) {
        lastResult = true;
      } else {
        lastResult = false;
      }
    }
  } else {
    lastResult = false;
  }
  ctx.body = { success: lastResult };
};

// 获取拣货员的单品订单和多品订单的可拣数量
const getSingleMoreOrderCount = async ctx => {
//   try {
    let body = ctx.request.body;
    console.log(body);
    let singleOrderSql = `SELECT COUNT(T2.YPBM) AS NUM FROM  (SELECT Y.YPBM  FROM YHDD Y   WHERE  Y.ID IN (SELECT T1.ID FROM (SELECT ID,YPBM FROM YHDD  
    GROUP BY ID,YPBM)T1 GROUP BY T1.ID  HAVING(COUNT(T1.YPBM)=1)) AND  Y.userId = ${body.userId} AND Y.JHZT = 'F' AND  Y.SFYC = 'F'  
    GROUP BY Y.YPBM,Y.carId,Y.JHZT) T2`;
    let moreSelectSql = `SELECT COUNT(T3.id) AS NUM FROM  (SELECT t1.id FROM (SELECT id, carid  FROM YHDD WHERE userid = ${body.userId} 
        AND JHZT = 'F' AND id <>ALL (SELECT id FROM YHDD WHERE SFYC = 'T')  GROUP BY id,carid) as t1   WHERE t1.id in (SELECT T2.ID FROM (SELECT ID,YPBM FROM YHDD  GROUP BY ID,YPBM)T2 
    GROUP BY T2.ID  HAVING(COUNT(T2.YPBM)>1)))T3`;
    let singleResult = await sql.query(singleOrderSql);
    let moreResult = await sql.query(moreSelectSql);
    console.log("singleResult", singleResult);
    console.log("moreSelectSql", moreSelectSql);
    ctx.body = {
      success: true,
      singleNum: singleResult.recordset[0].NUM,
      moreNum: moreResult.recordset[0].NUM
    };
//   } catch (err) {
//     ctx.body = {
//       success: false,
//       err
//     };
//   }
};

module.exports = {
  "POST /havePickGoodsSingleOrder": havePickGoodsSingleOrder,
  "POST /getPickGoodsSingleOrder": getPickGoodsSingleOrder,
  "POST /getAllSingleOrderInfo": getAllSingleOrderInfo,
  "GET /getPickGoodSingleOrderCount": getPickGoodSingleOrderCount,
  "POST /getSingleOrderDetailInfo": getSingleOrderDetailInfo,
  "POST /pickSingleGoods": pickSingleGoods,
  "POST /getPickGoodsUserAllSingleOrder": getPickGoodsUserAllSingleOrder,
  "POST /pickSingleOrderAbnormal": pickSingleOrderAbnormal,
  "POST /getSingleMoreOrderCount": getSingleMoreOrderCount
};
